﻿Imports Mystery.DbConnection
Imports System.Data.SqlClient

Public Class DefaultContentSaver(Of T As {New, IContent})
    Implements IContentSaver

    Private _content_type As ContentType
    Private _write_properties As IEnumerable(Of ContentProperty)
    Private _reference_properties As IEnumerable(Of ContentReference)
    Private _table_id As Integer?
    Private _property_instancer As New Dictionary(Of MysteryPropertyAttribute, IActivator)

    Private Sub setTableId()
        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        Dim result As DataTable = conn.executeQuery("select * from sys.tables where name='" & _content_type.name & "'")
        If result.Rows.Count = 0 Then Exit Sub
        _table_id = result.Rows(0)("object_id")
    End Sub

    Private _update_query As String
    Private _insert_query As String
    Private Const _par_prefix As String = "@ppp"

    Private _read_query As String

    Private ref_text As ReferenceText
    Private search_text As SearchText

    Sub New()
        _content_type = getMystery().AssemblyRegister.getMysteryClassAttribute(Of ContentType, T)()
        setTableId()

        Dim all As IEnumerable(Of ContentProperty) = getMystery().AssemblyRegister.getMysteryPropertyAttributes(Of ContentProperty, T)()
        _write_properties = New HashSet(Of ContentProperty)((From x As ContentProperty In all Select x Where x.retrive IsNot Nothing))
        Dim references As IEnumerable(Of ContentReference) = getMystery.AssemblyRegister.getMysteryPropertyAttributes(Of ContentReference, T)()
        _reference_properties = New HashSet(Of ContentReference)((From x As ContentReference In references Select x Where x.retrive IsNot Nothing))

        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        'check table exsistance
        If _table_id Is Nothing Then
            Dim creation_query As String = "CREATE TABLE [dbo].[" & _content_type.name & "]([id] [bigint] NOT NULL,CONSTRAINT [PK_" & _content_type.name & "] PRIMARY KEY CLUSTERED ([id] ASC)) ALTER TABLE [dbo].[" & _content_type.name & "]  WITH CHECK ADD  CONSTRAINT [FK_ID_" & _content_type.name & "] FOREIGN KEY([id]) REFERENCES [dbo].[Contents] ([id]) ON DELETE CASCADE"
            conn.executeNonQuery(creation_query)
            setTableId()
        End If

        'checking columns
        Dim result As DataTable = conn.executeQuery("select * from sys.columns where object_id = " & _table_id)
        Dim current_columns As New HashSet(Of String)
        For Each dr As DataRow In result.Rows
            current_columns.Add(dr("name"))
        Next

        Dim helper As DatabaseContentHelper = getGlobalObject(Of DatabaseContentHelper)()

        Dim fa As FastActivator = getGlobalObject(Of FastActivator)()

        For Each wp As ContentProperty In _write_properties
            'fedding _property_instancer
            Dim pt As Type = wp.used_in.PropertyType.GetGenericArguments.FirstOrDefault
            _property_instancer(wp) = fa.getActivator(wp.used_in.PropertyType)
            If current_columns.Contains(wp.name) Then Continue For
            conn.executeNonQuery("ALTER TABLE [" & _content_type.name & "] ADD [" & wp.name & "] " & helper.ContentPropertyDBType(pt))
            helper.FixesOnCreation(pt, _content_type.name, wp.name)
        Next


        search_text = GetType(T).getFirstAttribute(Of SearchText)()

        ref_text = GetType(T).getFirstAttribute(Of ReferenceText)()
        Dim ref_column_name As String = GetType(ReferenceText).Name
        If ref_text IsNot Nothing AndAlso Not current_columns.Contains(ref_column_name) Then
            conn.executeNonQuery("ALTER TABLE [" & _content_type.name & "] ADD [" & ref_column_name & "] nvarchar(1024)")
            helper.FixesOnCreation(GetType(String), _content_type.name, ref_column_name)
        End If

        For Each wp As ContentReference In _reference_properties
            'fedding _property_instancer
            Dim target_type As Type = wp.used_in.PropertyType.GetGenericArguments.FirstOrDefault
            _property_instancer(wp) = fa.getActivator(wp.used_in.PropertyType)
            Dim target_content_type As ContentType = getMystery.AssemblyRegister.getMysteryClassAttribute(Of ContentType)(target_type)

            'creating table
            If conn.executeQuery("select * from sys.tables where name='" & wp.tableName & "'").Rows.Count > 0 Then Continue For
            'not there let do it
            Dim creation_query As String = "CREATE TABLE [dbo].[" & wp.tableName & "]([id] [bigint] IDENTITY(1,1) NOT NULL,CONSTRAINT [PK_" & wp.tableName & "] PRIMARY KEY CLUSTERED ([id] ASC))"
            conn.executeNonQuery(creation_query)
            conn.executeNonQuery("ALTER TABLE [" & wp.tableName & "] ADD [source] [bigint] NOT NULL")
            conn.executeNonQuery("ALTER TABLE [" & wp.tableName & "] ADD [target] [bigint] NOT NULL")
            conn.executeNonQuery("ALTER TABLE [dbo].[" & wp.tableName & "]  WITH CHECK ADD  CONSTRAINT [FK_ID_" & wp.tableName & "_Source] FOREIGN KEY([source]) REFERENCES [dbo].[Contents] ([id]) ON DELETE CASCADE")
        Next

        'creating update query
        _update_query = "update [" & _content_type.name & "] set "
        Dim sets As New List(Of String)
        For Each wp As ContentProperty In _write_properties
            sets.Add(wp.name & "=" & _par_prefix & wp.name)
        Next
        If ref_text IsNot Nothing Then
            sets.Add(ref_column_name & "=" & _par_prefix & ref_column_name)
        End If
        _update_query &= String.Join(",", sets.ToArray) & " where id=@id"

        'insert query
        _insert_query = "insert into [" & _content_type.name & "] (id"
        If _write_properties.Count > 0 OrElse ref_text IsNot Nothing Then _insert_query &= ","
        sets = New List(Of String)
        For Each wp As ContentProperty In _write_properties
            sets.Add(wp.name)
        Next
        If ref_text IsNot Nothing Then
            sets.Add(ref_column_name)
        End If
        _insert_query &= String.Join(",", sets.ToArray) & ") values (@id"
        If _write_properties.Count > 0 OrElse ref_text IsNot Nothing Then _insert_query &= ","

        sets = New List(Of String)
        For Each wp As ContentProperty In _write_properties
            sets.Add(_par_prefix & wp.name)
        Next
        If ref_text IsNot Nothing Then
            sets.Add(_par_prefix & ref_column_name)
        End If
        _insert_query &= String.Join(",", sets.ToArray) & ")"



        'read query
        _read_query = "select * from [" & _content_type.name & "] as mytype,contents where contents.id=mytype.id and guid=@guid"

    End Sub

    Private Const select_from_contents As String = "select * from contents where guid=@guid"
    Private Const update_search As String = "update ContentSearch set search=@search where id=@id"
    Private Const insert_search As String = "insert into ContentSearch (id,search) values (@id,@search)"


    Public Function save(item As IContent) As CRUD? Implements IContentSaver.save
        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        Dim guidp As New SqlParameter("guid", item.guid.ToString)
        Dim result As DataTable = conn.executeQuery(select_from_contents, guidp)
        If result.Rows.Count = 0 Then
            saveFirstTime(item)
            Return CRUD.creation
        End If

        Dim id As Long = result.Rows(0)("id")
        Dim guid As Guid = guid.Parse(result.Rows(0)("guid"))
        Dim content_type_name As String = result.Rows(0)("content_type")

        If content_type_name <> _content_type.name Then
            remove(item)
            saveFirstTime(item)
            Return CRUD.update
        End If


        Dim values As New List(Of SqlParameter)
        For Each wp As ContentProperty In _write_properties
            Dim pv As ContentPropertyValue = wp.retrive(item)
            Dim value As Object = If(pv Is Nothing, DBNull.Value, pv.getValue())
            Dim sql_para As SqlParameter
            'I have dates and sql, when the hell they fix it, date.min is out of range in sql
            If TypeOf (value) Is Date AndAlso value = Date.MinValue Then
                sql_para = New SqlParameter()
                sql_para.ParameterName = _par_prefix & wp.name
                sql_para.DbType = DbType.DateTime
                sql_para.Value = DBNull.Value
            Else
                If value Is Nothing Then value = DBNull.Value
                sql_para = New SqlParameter(_par_prefix & wp.name, value)
            End If
            values.Add(sql_para)
        Next
        If ref_text IsNot Nothing Then
            Dim value As String = ref_text.getReferenceText(item)
            Dim sql_para As New SqlParameter(_par_prefix & GetType(ReferenceText).Name, value)
            values.Add(sql_para)
        End If

        Dim id_para As New SqlParameter("id", id)
        values.Add(id_para)
        Try
            conn.executeNonQuery(_update_query, values.ToArray)
        Catch ex As Exception
            Throw New Exception(_update_query)
        End Try


        'references
        Dim guid_para As New SqlParameter("guid", "hello")
        For Each rp As ContentReference In _reference_properties
            Try
                conn.executeNonQuery(" delete from [" & rp.tableName & "] where source=@id", id_para)
            Catch ex As Exception
                Throw New Exception(" delete from [" & rp.tableName & "] where source=@id")
            End Try

            Dim pv As BaseContentReference = rp.retrive()(item)
            For Each ref_guid As Guid In pv.getAsGuidEnum
                guid_para.Value = ref_guid.ToString
                Try
                    conn.executeNonQuery(" insert into [" & rp.tableName & "] (source,target) select top 1 @id, id from contents where [guid]=@guid", id_para, guid_para)
                Catch ex As Exception
                    Throw New Exception(" insert into [" & rp.tableName & "] (source,target) select top 1 @id, id from contents where [guid]=@guid")
                End Try

            Next
        Next

        'search
        If search_text IsNot Nothing Then
            Dim s As String = search_text.getSearchText(item)
            Dim p As New SqlParameter("search", s)
            Dim done As Boolean = conn.executeNonQuery(update_search, p, id_para)
            If Not done Then 'it is a content type which didn't have search text and now it does
                conn.executeNonQuery(insert_search, p, id_para)
            End If
        End If

        Return CRUD.update

    End Function


    Dim content_insert As String = "INSERT INTO [contents] ([guid],[content_type]) VALUES (@guid,@content_type)"

    Public Sub saveFirstTime(item As IContent)

        If item.guid = Guid.Empty Then item.guid = Guid.NewGuid
        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        Dim guidp As New SqlParameter("guid", item.guid.ToString)
        Try
            conn.executeNonQuery(content_insert, guidp, New SqlParameter("content_type", _content_type.name))
        Catch ex As Exception
            Throw New Exception(content_insert)
        End Try


        Dim result As DataTable = conn.executeQuery(select_from_contents, guidp)
        Dim id As Long = result.Rows(0)("id")

        Dim values As New List(Of SqlParameter)
        For Each wp As ContentProperty In _write_properties
            Dim pv As ContentPropertyValue = wp.retrive(item)
            Dim value As Object = If(pv Is Nothing, Nothing, pv.getValue())
            'smal date ahck
            If TypeOf (value) Is Date AndAlso Date.MinValue = value Then value = DBNull.Value
            If value Is Nothing Then value = DBNull.Value

            values.Add(New SqlParameter(_par_prefix & wp.name, value))
        Next
        If ref_text IsNot Nothing Then
            Dim value As String = ref_text.getReferenceText(item)
            Dim sql_para As New SqlParameter(_par_prefix & GetType(ReferenceText).Name, value)
            values.Add(sql_para)
        End If

        Dim id_para As New SqlParameter("id", id)
        values.Add(id_para)
        Try
            conn.executeNonQuery(_insert_query, values.ToArray)
        Catch ex As Exception
            Throw New Exception(_insert_query)
        End Try


        'references
        Dim guid_para As New SqlParameter("guid", "hello")
        For Each rp As ContentReference In _reference_properties
            conn.executeNonQuery(" delete from [" & rp.tableName & "] where source=@id", id_para)
            Dim pv As BaseContentReference = rp.retrive()(item)
            For Each ref_guid As Guid In pv.getAsGuidEnum
                guid_para.Value = ref_guid.ToString
                Try
                    conn.executeNonQuery(" insert into [" & rp.tableName & "] (source,target) select top 1 @id, id from contents where [guid]=@guid", id_para, guid_para)
                Catch ex As Exception
                    Throw New Exception("insert into [" & rp.tableName & "] (source,target) select top 1 @id, id from contents where [guid]=@guid")
                End Try

            Next
        Next

        'search
        If search_text IsNot Nothing Then
            Dim p As New SqlParameter("search", search_text.getSearchText(item))
            conn.executeNonQuery(insert_search, p, id_para)
        End If

    End Sub

    Private Const _delete_query As String = "delete from contents where guid=@guid"

    Public Function remove(item As IContent) As CRUD? Implements IContentSaver.remove
        If item.guid = Guid.Empty Then Return Nothing 'not in db yet

        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        Dim guidp As New SqlParameter("guid", item.guid.ToString)
        conn.executeNonQuery(_delete_query, guidp)
        Return CRUD.delete
    End Function



    Public Function read(guid As System.Guid) As IContent Implements IContentSaver.read
        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        Dim guidp As New SqlParameter("guid", guid.ToString)
        Dim dt As DataTable = conn.executeQuery(_read_query, guidp)
        If dt.Rows.Count = 0 Then
            Return Nothing
        End If

        Dim result As New T
        result.guid = guid

        Dim dr As DataRow = dt.Rows(0)

        For Each wp As ContentProperty In _write_properties
            Dim value_in_db As Object = dr(wp.name)
            If value_in_db Is DBNull.Value Then value_in_db = Nothing
            Dim pv As ContentPropertyValue = _property_instancer(wp).createInstance
            pv.setValue(value_in_db)
            wp.save()(result, pv)
        Next

        For Each rp As ContentReference In _reference_properties
            If rp.isReadOnly Then Continue For
            dt = conn.executeQuery(" select source.guid as s_guid,target.guid as t_guid from contents as source,[" & rp.tableName & "] as ref,contents as target where source.id = ref.source and ref.target = target.id and source.guid=@guid", guidp)
            Dim pv As BaseContentReference = rp.retrive()(result)
            Dim guids As IEnumerable(Of Guid) = (From x As DataRow In dt.Rows Select guid.Parse(x("t_guid")))
            pv.setWithGuids(guids)
        Next

        Return result
    End Function

    Public Function getAll(filter As IContentFilter) As System.Collections.Generic.IEnumerable(Of IContent) Implements IContentSaver.getAll

        Dim query As String = "select distinct guid,Contents.id from Contents,[" & _content_type.name & "] where Contents.id=[" & _content_type.name & "].id "
        Dim parameters As New List(Of SqlParameter)
        Dim p_index As Integer = 0

        Dim template As IContent = filter.filter

        For Each wp As ContentProperty In _write_properties
            If wp.isReadOnly Then Continue For
            Dim filter_value As ContentPropertyValue = Nothing
            Try
                filter_value = wp.retrive()(template)
            Catch ex As Exception
                'if it fails it because the template is not the sale type and we are accessing a property not present in this one
            End Try

            If filter_value Is Nothing OrElse Not filter_value.isFilter Then Continue For
            Dim filter_instance As IContentPropertyFilter = filter_value
            Dim values As IEnumerable(Of Object) = filter_instance.getValues
            Dim contrains As New List(Of String)
            For Each v As Object In values
                Dim pname As String = _par_prefix & p_index
                contrains.Add(wp.name & "=" & pname)
                'smal date ahck
                If TypeOf (v) Is Date AndAlso Date.MinValue = v Then v = DBNull.Value
                If v Is Nothing Then v = DBNull.Value
                parameters.Add(New SqlParameter(pname, v))
                p_index += 1
            Next
            Dim this_property As String = String.Join(" or ", contrains.ToArray)
            query &= " and ( " & this_property & ") "
        Next


        Dim references_tables As New List(Of String)
        For Each rp As ContentReference In _reference_properties
            If rp.isReadOnly Then Continue For
            Dim filter_value As ContentPropertyValue = Nothing
            Try
                filter_value = rp.retrive()(template)
            Catch ex As Exception
                'if it fails it because the template is not the sale type and we are accessing a property not present in this one
            End Try

            If filter_value Is Nothing OrElse Not filter_value.isFilter Then Continue For
            Dim filter_instance As IContentPropertyFilter = filter_value
            Dim values As IEnumerable(Of IContent) = New HashSet(Of IContent)(From x As IContent In filter_instance.getValues)
            Dim contrains As New List(Of String)
            For Each v As IContent In values
                Dim pname As String = _par_prefix & p_index
                If v Is Nothing Then
                    contrains.Add(" [guid] is NULL ")
                Else
                    parameters.Add(New SqlParameter(pname, v.guid.ToString))
                    contrains.Add(" [guid]=" & pname)
                    p_index += 1
                End If
            Next
            references_tables.Add(" select id,guid  from [" & _content_type.name & "] left join (select [" & rp.tableName & "].source,contents.guid from [" & rp.tableName & "],contents where [" & rp.tableName & "].target = contents.id) as t1 on [" & _content_type.name & "].id=t1.source where " & String.Join(" or ", contrains.ToArray))
        Next


        Dim references_tables_indexes As New List(Of Integer)

        If references_tables.Count > 0 Then

            Dim table_prefix As String = "my_table"
            Dim table_index As Integer = 0
            query = "with " & table_prefix & table_index & " as ( " & query & "), "
            table_index += 1
            For Each ref As String In references_tables
                query &= table_prefix & table_index & " as ( " & ref & "), "
                references_tables_indexes.Add(table_index)
                table_index += 1
            Next
            Dim inner_joints As New List(Of String)

            Dim index1 As Integer = 0
            For Each index2 As String In references_tables_indexes
                inner_joints.Add(table_prefix & table_index & " as ( select " & table_prefix & index1 & ".id," & table_prefix & index1 & ".guid from " & table_prefix & index1 & "," & table_prefix & index2 & " where " & table_prefix & index1 & ".id=" & table_prefix & index2 & ".id )")
                index1 = table_index
                table_index += 1
            Next
            query &= String.Join(", ", inner_joints.ToArray) & " select distinct guid,id from " & table_prefix & (table_index - 1)
        End If


        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        Dim dt As DataTable = conn.executeQuery(query, parameters.ToArray)
        Dim cd As IContentDispatcher = getGlobalObject(Of IContentDispatcher)()
        Dim result As New List(Of IContent)(dt.Rows.Count)
        For Each dr As DataRow In dt.Rows
            Dim content As IContent = cd.getContent(Of T)(Guid.Parse(CStr(dr(0))))
            If content Is Nothing Then Continue For
            result.Add(content)
        Next
        Return result
    End Function

    Public Function haveOne() As Boolean Implements IContentSaver.haveOne
        Dim query As String = "select top 1 guid from contents,[" & _content_type.name & "] where contents.id = [" & _content_type.name & "].id"
        Dim conn As IConnection = getGlobalObject(Of IConnection)()
        Return conn.executeQuery(query).Rows.Count > 0
    End Function
End Class
